﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using JuCheap.Core.Interfaces;
using JuCheap.Core.Models.Filters;
using JuCheap.Core.Models.ViewModels;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;

namespace JuCheap.Core.Web.Controllers
{
    public class XlsxController : Controller
    {
        private IReportFormService _iReportFormService;
        private IRepository _iRepository;
        public IActionResult Index()
        {
            return View();
        }
        private IHostingEnvironment _hostingEnvironment;

        public XlsxController(IHostingEnvironment hostingEnvironment, IReportFormService iRepositoryService,
            IRepository iRepository)
        {
            _hostingEnvironment = hostingEnvironment;
            _iReportFormService = iRepositoryService;
            _iRepository = iRepository;
        }
        public IActionResult Export()
        {
            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "ID";
                worksheet.Cells[1, 2].Value = "Name";
                worksheet.Cells[1, 3].Value = "Url";
                //添加值
                worksheet.Cells["A2"].Value = 1000;
                worksheet.Cells["B2"].Value = "LineZero";
                worksheet.Cells["C2"].Value = "http://www.cnblogs.com/linezero/";

                worksheet.Cells["A3"].Value = 1001;
                worksheet.Cells["B3"].Value = "LineZero GitHub";
                worksheet.Cells["C3"].Value = "https://github.com/linezero";
                worksheet.Cells["C3"].Style.Font.Bold = true;

                package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }
        public static string ToName(int index)
        {
            if (index < 0) { throw new Exception("invalid parameter"); }

            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }

        public IActionResult ExportWorker(OriganizationReportFilter filter)
        {
            if (string.IsNullOrEmpty(filter.orginationName) ||
                filter.orginationName == "请选择")
                filter.orginationName = "";

            var result =  _iReportFormService.GetReport(filter).Result;

            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"worker_{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "公司";
                worksheet.Cells[1, 2].Value = "状态";
                if(result.rows.Count()>0)
                {
                    for(int i =0; i< result.rows[0].Employers.Count(); i++)
                    {
                        worksheet.Cells[1, i+3].Value = result.rows[0].Employers.ElementAt(i).Key;
                    }
                }
                //int j = 2;
                for (int j= 0; j < result.rows.Count();j++)
                {
                    worksheet.Cells[$"A{j+2}"].Value = result.rows[j].OrganizationName;
                    worksheet.Cells[$"B{j+2}"].Value = result.rows[j].Status;
                    for (int i = 0; i < result.rows[0].Employers.Count(); i++)
                    {
                        worksheet.Cells[$"{ToName(i+2)}{j+2}"].Value = result.rows[j].Employers.ElementAt(i).Value;
                    }
                }
            
                package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
           
        }


        public  IActionResult GetWorkerAttend(WorkerAttendFilter filter)
        {
            var workerFilter = new WorkerFilter
            {
                name = filter.Name,
                CompanyId = filter.CompanyId,
                JobPositionTypeId = filter.JobPositionTypeId,
                page = filter.page,
                rows = 10000,
                keywords = ""
            };
            List<WorkAttendance> result = new List<WorkAttendance>();
            var workerList = _iRepository.GetWorkers(workerFilter).Result;
            List<string> li = new List<string>();
            foreach (var worker in workerList.rows)
            {
                result.Add(new WorkAttendance()
                {
                    WorkerName = worker.Name,
                    JobName = worker.JobPositionName,
                });
                li.Add(worker.Name);
            }
            filter.WorkName = li;

            var year = filter.Year;
            var month = filter.Month;
            filter.Date = new DateTime(year, month, 1).ToShortDateString();
            var attendance = _iReportFormService.GetWorkerAttend(filter);
            foreach (var worker in result)
            {
                var w = attendance.Where(a => a.Worker.Name == worker.WorkerName).ToList();

                for (DateTime dt = new DateTime(year, month, 1); dt < new DateTime(year, month + 1, 1); dt = dt.AddDays(1))
                {
                    var d = w.Where(a => a.Date == dt).FirstOrDefault();
                    if (d == null)
                    {
                        worker.workerDates.Add(new WorkerDate()
                        {
                            Date = dt.Date.ToString("yyyy-MM-dd"),
                            FirstTime = "",
                            LastTime = "",
                        });
                    }
                    else
                    {
                        worker.workerDates.Add(new WorkerDate()
                        {
                            Date = d.Date.ToString("yyyy-MM-dd"),
                            FirstTime = d.FirstTime == null ? "" : d.FirstTime.Value.Hour.ToString(),
                            LastTime = d.LastTime == null ? "" : d.LastTime.Value.Hour.ToString(),
                        });
                    }
                }

            }

            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"worker_{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "员工姓名";
                worksheet.Cells[1, 2].Value = "岗位名称";
                int clom = 0;
                for (int i = 0; i < result[0].workerDates.Count; i++)
                {
                    worksheet.Cells[1, clom + 3].Value = result[0].workerDates[i].Date + "上午";
                    worksheet.Cells[1, clom + 4].Value = result[0].workerDates[i].Date + "下午";
                    clom = clom + 2;
                }
                clom = 0;
                for (int j = 0; j < result.Count; j++)
                {
                    worksheet.Cells[$"A{j + 2}"].Value = result[j].WorkerName;
                    worksheet.Cells[$"B{j + 2}"].Value = result[j].JobName;
                    
                    for (int i = 0; i < result[j].workerDates.Count; i++)
                    {

                        worksheet.Cells[$"{ToName(clom + 2)}{j + 2}"].Value = result[j].workerDates[i].FirstTime;
                        worksheet.Cells[$"{ToName(clom + 3)}{j + 2}"].Value = result[j].workerDates[i].LastTime;
                        clom = clom + 2;
                    }
                }

                    package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }


        public  IActionResult GetWorkerStatistic(WorkerAttendFilter filter)
        {
            var workerFilter = new WorkerFilter
            {
                name = filter.Name,
                CompanyId = filter.CompanyId,
                JobPositionTypeId = filter.JobPositionTypeId,
                page = filter.page,
                rows = 10000,
                keywords = ""
            };
            List<WorkerStatistic> result = new List<WorkerStatistic>();
            var workerList = _iRepository.GetWorkers(workerFilter).Result;
            List<string> li = new List<string>();
            foreach (var worker in workerList.rows)
            {
                result.Add(new WorkerStatistic()
                {
                    WorkerName = worker.Name,
                    JobName = worker.JobPositionName,
                });
                li.Add(worker.Name);
            }
            filter.WorkName = li;

            var year = filter.Year;
            var month = filter.Month;
            filter.Date = new DateTime(year, month, 1).ToShortDateString();
            var attendance = _iReportFormService.GetWorkerAttend(filter);
            foreach (var worker in result)
            {
                var attend = attendance.Where(a => a.Worker.Name == worker.WorkerName).ToList();
                worker.AddedHour = attend.Sum(a => a.AddedHour);
                worker.TotalHour = attend.Sum(a => a.TotalHour);
                worker.AllHour = worker.AddedHour + worker.TotalHour;
             

            }

            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"worker_{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "员工姓名";
                worksheet.Cells[1, 2].Value = "岗位名称";
                worksheet.Cells[1, 3].Value = "正常班工时";
                worksheet.Cells[1, 4].Value = "加班工时";
                worksheet.Cells[1, 5].Value = "累计工时";

                for (int j = 0; j < result.Count; j++)
                {
                    worksheet.Cells[$"A{j + 2}"].Value = result[j].WorkerName;
                    worksheet.Cells[$"B{j + 2}"].Value = result[j].JobName;
                    worksheet.Cells[$"C{j + 2}"].Value = result[j].TotalHour;
                    worksheet.Cells[$"D{j + 2}"].Value = result[j].AddedHour;
                    worksheet.Cells[$"E{j + 2}"].Value = result[j].AllHour;
                }

                package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }



        private int GetCompanyId(string cName, List<CompanyViewModel> li)
        {
            var result = li.Where(l => l.Name == cName).FirstOrDefault();
            if (result == null)
                return 1;
            return result.Id;
        }

        private int GetGender(string sex)
        {
            if (sex == "男")
                return 1;
            return 0;
        }

        private int GetJobPositionTypeId(string cName, List<JobPositionTypeViewModel> li)
        {
            var result = li.Where(l => l.Name == cName).FirstOrDefault();
            if (result == null)
                return 1;
            return result.Id;
        }

        private int GetJobPositionId(string cName, List<JobPositionViewModel> li)
        {
            var result = li.Where(l => l.Name == cName).FirstOrDefault();
            if (result == null)
                return 1;
            return result.Id;
        }
        private string GetExcelValue(ExcelRange range)
        {
            if (range == null)
                return string.Empty;
            return range.Text ;
        }

        [HttpPost]
        public IActionResult Import(IFormFile excelfile)
        {
            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"{Guid.NewGuid()}.xlsx";
            var filter = new CompanyFilter
            {
                page = 1,
                rows = 1000,
                keywords = ""
            };
            var companies = _iRepository.GetCompanys(filter).Result.rows.ToList();
            var jobPositionTypes = _iRepository.GetJobPositionTypes(new JobPositionTypeFilter()
            {
                page = 1,
                rows = 1000,
                keywords = ""
            }).Result.rows.ToList(); 
            var jobs = _iRepository.GetJobPositions(new JobPositionFilter()
            {
                page = 1,
                rows = 1000,
                keywords = ""
            }).Result.rows.ToList(); 


        

            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            try
            {
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    excelfile.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    //bool bHeaderRow = true;
                    for (int row = 2; row <= rowCount; row++)
                    {
                        WorkerViewModel model = new WorkerViewModel()
                        {
                            Name = GetExcelValue(worksheet.Cells[row, 1]),
                            PhoneNumber = GetExcelValue(worksheet.Cells[row, 2]),
                            CompanyId = GetCompanyId(worksheet.Cells[row, 3].Value.ToString(), companies), //公司名称
                            JobPositionTypeId = GetJobPositionTypeId(worksheet.Cells[row, 4].Value.ToString(), jobPositionTypes),
                            JobPositionId = GetJobPositionId(worksheet.Cells[row, 5].Value.ToString(), jobs),
                            ExtenralId = GetExcelValue(worksheet.Cells[row, 6]),
                            CardNumber = GetExcelValue(worksheet.Cells[row, 7]),
                            Birthday = Convert.ToDateTime(worksheet.Cells[row, 8].Value.ToString()),
                            Gender = GetGender(worksheet.Cells[row, 9].Value.ToString()),
                            Email = GetExcelValue(worksheet.Cells[row, 10]),
                            NickName = GetExcelValue(worksheet.Cells[row, 11]),
                            Remark = GetExcelValue(worksheet.Cells[row, 12]),
                            GroupIds = GetExcelValue(worksheet.Cells[row, 13]),
                            CardType = 1,//证件类型 身份证1
                            Status=1,
                        };
                        var re = _iRepository.AddWorker(model).Result;

                        sb.Append(Environment.NewLine);
                    }
                    return Content(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }


        [HttpPost]
        public IActionResult ImportImportGroup(IFormFile excelfile)
        {
            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            try
            {
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    excelfile.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    //bool bHeaderRow = true;

                    List<KeyValuePair<string, string>> workers = new List<KeyValuePair<string, string>>();
                    for (int row = 2; row <= rowCount; row++)
                    {
                        workers.Add(new KeyValuePair<string, string>(GetExcelValue(worksheet.Cells[row, 1])
                            , GetExcelValue(worksheet.Cells[row, 2])));


                    }
                    var re = _iRepository.ImportWorkerToGroup(workers);

                    sb.Append(Environment.NewLine);
                    return Content(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }
    }
}